package my.guojian.jdbc.util;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Vector;
import java.util.regex.Matcher;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import my.guojian.jdbc.bean.ConnectionBean;
import my.guojian.jdbc.connection.ConnectionPool;
import my.guojian.jdbc.transaction.TransactionCallBack;
import my.guojian.jdbc.transaction.TransactionUtil;

/**
 * 
 * @author guojian
 *
 */
@SuppressWarnings("unchecked")
public class JdbcUtil extends AbstractorJdbcExecute {
	private static final Log log = LogFactory.getLog(JdbcUtil.class);

	public static List<Object> formatBeanToFields(Object bean, Matcher matcher) {
		List<Object> fields = new ArrayList<Object>();
		try {
			while (matcher.find()) {
				Field field = bean.getClass().getDeclaredField(matcher.group(1));
				field.setAccessible(true);
				fields.add(field.get(bean));
			}
		} catch (SecurityException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (NoSuchFieldException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		}
		return fields;
	}

	@SuppressWarnings("unused")
	private static int getFieldCount(Matcher matcher) {
		int fieldCount = 0;
		while (matcher.find()) {
			fieldCount++;
		}
		matcher.reset();
		return fieldCount;
	}

	public static <T> List<T> buildSqlForListBean(ConnectionBean con, String sql, final Class<T> c, List<Object> fields, String typeName) {
		Object result = execute(new JdbcExecute() {
			@Override
			public Object[] execute(Connection con, String sql, List<Object> fields) throws Exception {
				PreparedStatement statement = con.prepareStatement(sql);
				if (fields != null) {
					for (int i = 0; i < fields.size(); i++) {
						statement.setObject(i + 1, fields.get(i));
					}
				}
				ResultSet result = statement.executeQuery();
				ResultSetMetaData tableData = result.getMetaData();
				int cloumnCounts = tableData.getColumnCount();
				List<T> objs = new ArrayList<T>();
				while (result.next()) {
					T obj = c.newInstance();
					for (int i = 1; i <= cloumnCounts; i++) {
						if (result.getObject(i) != null) {
							Field field = null;
							try {
								field = c.getDeclaredField(StringUtils.uncapitalize(tableData.getColumnLabel(i)));
								field.setAccessible(true);
								field.set(obj, result.getObject(i));
							} catch (Exception e) {
								log.info(c.getName() + "--->" + e);
							}
						}
					}
					objs.add(obj);
				}
				return new Object[] { statement, result, objs };
			}
		}, con, sql, typeName, fields);

		if (result != null) {
			return (List<T>) result;
		}
		return Collections.emptyList();
	}

	public static List<Object[]> buildSqlForListField(ConnectionBean con, String sql, List<Object> fields, String typeName) {
		Object result = execute(new JdbcExecute() {
			@Override
			public Object[] execute(Connection con, String sql, List<Object> fields) throws Exception {
				PreparedStatement statement = con.prepareStatement(sql);
				if (fields != null) {
					for (int i = 0; i < fields.size(); i++) {
						statement.setObject(i + 1, fields.get(i));
					}
				}
				ResultSet result = statement.executeQuery();
				ResultSetMetaData tableData = result.getMetaData();
				int cloumnCounts = tableData.getColumnCount();
				List<Object[]> objs = new ArrayList<Object[]>();
				while (result.next()) {
					Object[] obj = new Object[cloumnCounts];
					for (int i = 1; i <= cloumnCounts; i++) {
						obj[i - 1] = result.getObject(i);
					}
					objs.add(obj);
				}
				return new Object[] { statement, result, objs };
			}
		}, con, sql, typeName, fields);

		if (result != null) {
			return (List<Object[]>) result;
		}
		return Collections.emptyList();
	}

	public static Vector<Object> buildSqlForVectorField(ConnectionBean con, String sql, List<Object> fields, String typeName) {
		Object result = execute(new JdbcExecute() {
			@Override
			public Object[] execute(Connection con, String sql, List<Object> fields) throws Exception {
				PreparedStatement statement = con.prepareStatement(sql);
				if (fields != null) {
					for (int i = 0; i < fields.size(); i++) {
						statement.setObject(i + 1, fields.get(i));
					}
				}
				ResultSet result = statement.executeQuery();
				ResultSetMetaData tableData = result.getMetaData();
				int cloumnCounts = tableData.getColumnCount();
				Vector<Object> objs = new Vector<Object>();
				while (result.next()) {
					Vector<Object> rowData = new Vector<Object>();
					for (int i = 1; i <= cloumnCounts; i++) {
						rowData.add(result.getObject(i));
					}
					objs.add(rowData);
				}
				return new Object[] { statement, result, objs };
			}
		}, con, sql, typeName, fields);

		if (result != null) {
			return (Vector<Object>) result;
		}
		return new Vector<Object>(0);
	}

	public static List<Map<String, Object>> buildSqlForMapField(ConnectionBean con, String sql, List<Object> fields, String typeName) {
		Object result = execute(new JdbcExecute() {
			@Override
			public Object[] execute(Connection con, String sql, List<Object> fields) throws Exception {
				PreparedStatement statement = con.prepareStatement(sql);
				if (fields != null) {
					for (int i = 0; i < fields.size(); i++) {
						statement.setObject(i + 1, fields.get(i));
					}
				}
				ResultSet result = statement.executeQuery();
				ResultSetMetaData tableData = result.getMetaData();
				int cloumnCounts = tableData.getColumnCount();
				List<Map<String, Object>> mapList = new ArrayList<Map<String, Object>>();
				while (result.next()) {
					Map<String, Object> map = new LinkedHashMap<String, Object>();
					for (int i = 1; i <= cloumnCounts; i++) {
						map.put(tableData.getColumnName(i), result.getObject(i));
					}
					mapList.add(map);
				}
				return new Object[] { statement, result, mapList };
			}
		}, con, sql, typeName, fields);

		if (result != null) {
			return (List<Map<String, Object>>) result;
		}
		return Collections.emptyList();
	}

	public static Vector<String> getTableColumnNameBySql(ConnectionBean con, String sql, String typeName) {
		Object result = execute(new JdbcExecute() {
			@Override
			public Object[] execute(Connection con, String sql, List<Object> fields) throws Exception {
				PreparedStatement statement = con.prepareStatement(sql);
				ResultSet result = statement.executeQuery();
				ResultSetMetaData tableData = result.getMetaData();
				int cloumnCounts = tableData.getColumnCount();
				Vector<String> objs = new Vector<String>();
				if (result.next()) {
					for (int i = 1; i <= cloumnCounts; i++) {
						objs.add(tableData.getColumnName(i));
					}
				}
				return new Object[] { statement, result, objs };
			}
		}, con, sql, typeName, null);
		if (result != null) {
			return (Vector<String>) result;
		}
		return new Vector<String>(0);

	}

	public static boolean executeUpdate(ConnectionBean con, String sql, List<Object> fields, String typeName) {
		Object result = execute(new JdbcExecute() {
			@Override
			public Object[] execute(Connection con, String sql, List<Object> fields) throws Exception {
				PreparedStatement statement = fillPrepareStatementValue(con.prepareStatement(sql), fields);
				return new Object[] { statement, null, statement.executeUpdate() > 0 };
			}
		}, con, sql, typeName, fields);
		if (result != null) {
			return Boolean.parseBoolean(result.toString());
		}
		return false;
	}

	private static PreparedStatement fillPrepareStatementValue(PreparedStatement statement, List<Object> fields) throws SQLException {
		if (fields != null) {
			for (int i = 0; i < fields.size(); i++) {
				Object field = fields.get(i);
				if (field instanceof Date) {
					field = DateFormatUtils.format((Date) field, "yyyy-MM-dd HH:mm:ss");
				}
				statement.setObject(i + 1, field);
			}
		}
		return statement;
	}

	public static void executeUpdateForTransaction(Connection con, String sql, List<Object> fields) throws SQLException {
		PreparedStatement preStatement = fillPrepareStatementValue(con.prepareStatement(sql), fields);
		preStatement.executeUpdate();
		List<Statement> statements = TransactionUtil.statementThread.get();
		if (statements == null) {
			statements = new ArrayList<Statement>();
			statements.add(preStatement);
			TransactionUtil.statementThread.set(statements);
		} else {
			statements.add(preStatement);
		}
	}

	/**
	 * JDBC 事务
	 */
	public static void beginJdbcTransaction(Connection con, TransactionCallBack tc, String typeName) {
		Statement statement = null;
		try {
			con.setAutoCommit(false);
			statement = tc.execute(con);
			con.commit();
		} catch (SQLException e) {
			try {
				con.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			log.debug("===========已经回滚================", e);
		} finally {
			try {
				con.setAutoCommit(true);
			} catch (SQLException e) {
				e.printStackTrace();
			}
			ConnectionPool.closeConnection(con, typeName, null, statement);
		}
	}

	// 检测连接是否有效
	public static boolean isConOk(Connection con, String typeName) {
		PreparedStatement statement = null;
		ResultSet result = null;
		try {
			statement = con.prepareStatement(" select 1 ");
			result = statement.executeQuery();
			while (result.next()) {
				return true;
			}
		} catch (Exception e) {
			ConnectionPool.clearAllConnection(typeName);
		} finally {
			ConnectionPool.closeConnection(con, typeName, result, statement);
		}
		return false;
	}

}
